package com.coach.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;


/**
 * 璐熻矗杩炴帴鏁版嵁搴� 
 * 濡備綍灏佽 锛燂紵 閭ｄ簺鏄彉鐨� 锛�  SQL鏄彉鍖�   鍙傛暟鏄彉鍖�     鏌ヨ鍜� DML鏄笉鍚岀殑   閭ｄ簺鏄笉鍙樼殑 锛�     椹卞姩鏄笉鍙樺寲  杩炴帴鏄笉鍙樼殑  鍏抽棴 涔熸槸涓嶅彉鐨�
 * @author SZT
 *
 */
public class SqlUtil {
	
	private  static  final  String  DRIVER="driver";
	private  static  final  String  URL ="url";
	private  static  final  String  USERNAME="username";
	private  static  final  String  PASSWORD="password";
	static  Properties proper =new Properties();
	//鏈湴鍙橀噺  鎴栬�呯嚎绋嬪彉閲�
	static  ThreadLocal<Connection>   container=new  ThreadLocal<Connection>();
	
	//棣栧厛
	static{
		
		try {
			//閫氳繃绫诲姞杞藉櫒 鍔犺浇鍦ㄧ被璺熻矾寰勪笅鐨勫睘鎬ч厤缃枃浠�
			proper.load(SqlUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	//1銆佸姞杞介┍鍔�
	static {
		try {
			Class.forName(proper.getProperty(DRIVER));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//2銆佽幏鍙栬繛鎺�
	public static Connection   getConnection(){
		Connection  conn =container.get();
		try {
			if(conn==null  || conn.isClosed()){
				conn =	DriverManager.getConnection(proper.getProperty(URL),
						proper.getProperty(USERNAME),proper.getProperty(PASSWORD));
				container.set(conn);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  conn;
	}
	//查询
	public  static ArrayList<HashMap<String,Object>>    select(String sql,Object ... params){
		ArrayList<HashMap<String,Object>>  list=new  ArrayList<HashMap<String,Object>>();
		Connection conn =	getConnection();
		PreparedStatement pst=null;
		ResultSet  rs=null;
		try {
			pst  =	conn.prepareStatement(sql);
			
			if(params!=null){
				for(int i =0;i<params.length;i++){
					pst.setObject(i+1, params[i]);
				}
			}
			rs =  pst.executeQuery();
			//瑙ｆ瀽ResultSet 瑙ｆ瀽鎴怘ashMap 鍜孉rrayList鐨勭粍鍚�  涓�鏉℃暟鎹瓾ashMap 澶氭潯鏁版嵁鏄疉rrayList
			int count = rs.getMetaData().getColumnCount(); //鑾峰彇鍒楃殑鏁伴噺
			System.out.println(count);
			while(rs.next()){
				HashMap<String ,Object>  row =new  HashMap<String ,Object> ();
				for(int i=0;i<count;i++){
					String key =rs.getMetaData().getColumnLabel(i+1);
					Object  value =rs.getObject(key);
					row.put(key, value);
				}
				list.add(row);
			}
			
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(pst!=null){
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return  list;
		
		
	}
	
	
	//更新
	public  static int    update(String sql,Object ... params){
		Connection conn =	getConnection();
		int row=0;
		PreparedStatement pst=null;
		
		try {
			pst  =	conn.prepareStatement(sql);
			if(params!=null){
				for(int i =0;i<params.length;i++){
					pst.setObject(i+1, params[i]);
				}
			}
			row =  pst.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(pst!=null){
				try {
					pst.close();
					pst=null;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return  row;
		
		
	}
	
	//鍏抽棴璧勬簮
	public  static void   close(){
		if(container.get()!=null){
			try {
				container.get().close();
				container.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	
	
	
}
